package com.example.function.integration.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.function.integration.exception.BizException;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SheetDataWriter;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
import java.util.concurrent.Future;

import static com.example.function.integration.constant.CommonConstant.*;
import static com.example.function.integration.utils.DateUtils.YYYY_MM_DD;

/**
 * @Description：Excel导入导出工具类
 * @Author：fengcheng
 * @Date：2024/10/16 14:21
 */
@Slf4j
public class ExcelUtils {

    public static final String LOCAL_FILE_PATH = "/export/data/huaneng";

    /**
     * @Description：解析文件中的数据，并返回JSON数组
     * @Author：fengcheng
     * @Date：2024/10/16 14:45
     * @Param：file
     * @Param：linesStart 起始行，默认0
     * @ParamsType：[org.springframework.web.multipart.MultipartFile, java.io.File]
     * @Return：com.alibaba.fastjson.JSONArray
     */
    public static JSONArray readExcelToJsonArray(MultipartFile mFile, Integer linesStart) throws IOException {
        if (ObjectUtils.isEmpty(mFile)) {
            return null;
        }
        Workbook book = WorkbookFactory.create(mFile.getInputStream());
        if (book == null) {
            return new JSONArray();
        }
        return readSheetToJsonArray(book.getSheetAt(0), linesStart);
    }

    /**
     * @Description：解析CSV文件中的数据，返回指定Class集合,根据tClass和fields参数进行转换，将excel数据转换为java对象，
     * 注意：【fields】字段顺序必须与excel文件中的列顺序一致，否则会导致数据转换错误
     * @Author：fengcheng
     * @Date：2024/12/11 13:53
     * @Param：mFile
     * @Param：linesStart 从第几行开始解析数据，默认0
     * @Param：tClass 表示要转换的类型
     * @Param：fields 导入数据的字段
     * @ParamsType：[org.apache.poi.ss.usermodel.Sheet, java.lang.Integer, java.lang.Class<T>, java.util.List<java.lang.String>]
     * @Return：java.util.List<T>
     */
    public static <T> List<T> readExcelToGenericsList(MultipartFile mFile, Integer linesStart, Class<T> tClass, List<String> fields) throws IOException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, NoSuchFieldException {
        if (ObjectUtils.isEmpty(mFile)) {
            return null;
        }
        Workbook book = WorkbookFactory.create(mFile.getInputStream());
        if (book == null) {
            return Lists.newArrayList();
        }
        return readSheetToGenericsList(book.getSheetAt(0), linesStart, tClass, fields);
    }

    /**
     * @Description：解析文件中数据，并返回List集合
     * @Author：fengcheng
     * @Date：2024/10/16 14:40
     * @Param：mFile 解析的文件
     * @Param：file 默认传null（表示本地文件）
     * @ParamsType：[org.springframework.web.multipart.MultipartFile, java.io.File]
     * @Return：java.util.List<java.util.List<java.lang.String>>
     */
    public static List<List<String>> readExcelToList(MultipartFile mFile, Integer linesStart) throws IOException {
        if (ObjectUtils.isEmpty(mFile)) {
            return null;
        }
        Workbook book = WorkbookFactory.create(mFile.getInputStream());
        if (book == null) {
            return Lists.newArrayList();
        }
        return readSheetToList(book.getSheetAt(0), linesStart);
    }

    /**
     * @Description：读取文件中sheet页中的数据，返回JSON数组
     * @Author：fengcheng
     * @Date：2024/10/16 15:11
     * @Param：sheet
     * @ParamsType：[org.apache.poi.ss.usermodel.Sheet]
     * @Return：com.alibaba.fastjson.JSONArray
     */
    private static JSONArray readSheetToJsonArray(Sheet sheet, Integer linesStart) {
        Integer rowStart = null;
        if (linesStart != 0) {
            rowStart = linesStart;
        } else {
            // 首行下标
            rowStart = sheet.getFirstRowNum();
        }
        // 尾行下标
        int rowEnd = sheet.getLastRowNum();
        // 获取表头行
        Row headRow = sheet.getRow(rowStart);
        if (headRow == null) {
            return new JSONArray();
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<>();
        for (int j = cellStart; j < cellEnd; j++) {
            // 获取表头数据
            String val = getCellValue(headRow.getCell(j));
            if (val != null && !val.trim().isEmpty()) {
                keyMap.put(j, val);
            }
        }
        // 如果表头没有数据则不进行解析
        if (keyMap.isEmpty()) {
            return new JSONArray();
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (rowStart == rowEnd) {
            JSONObject obj = new JSONObject();
            for (int i : keyMap.keySet()) {
                obj.put(keyMap.get(i), "");
            }
            array.add(obj);
            return array;
        }
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            /*
            // 添加行号
            obj.put(ROW_NUM, i + 1);
            */
            StringBuilder sb = new StringBuilder();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getCellValue(eachRow.getCell(k));
                    // 所有数据添加到里面，用于判断该行是否为空
                    sb.append(val);
                    obj.put(keyMap.get(k), val);
                }
            }
            if (sb.length() > 0) {
                array.add(obj);
            }
        }
        return array;
    }

    /**
     * @Description：解析CSV文件中的数据，返回指定Class集合,根据tClass和fields参数进行转换，将excel数据转换为java对象，注意：【fields】字段顺序必须与excel文件中的列顺序一致，否则会导致数据转换错误
     * @Author：fengcheng
     * @Date：2024/12/11 12:02
     * @Param：sheet
     * @Param：linesStart 从第几行开始解析数据，默认0
     * @Param：tClass 表示要转换的类型
     * @Param：fields 导入数据的字段
     * @ParamsType：[org.apache.poi.ss.usermodel.Sheet, java.lang.Integer, java.lang.Class<T>, java.util.List<java.lang.String>]
     * @Return：java.util.List<T>
     */
    private static <T> List<T> readSheetToGenericsList(Sheet sheet, Integer linesStart, Class<T> tClass, List<String> fields) throws InstantiationException, IllegalAccessException, NoSuchFieldException {
        List<T> genericsList = Lists.newArrayList();
        int rowStart;
        if (linesStart != 0) {
            rowStart = linesStart;
        } else {
            rowStart = sheet.getFirstRowNum();
        }
        int rowEnd = sheet.getLastRowNum();
        Row headRow = sheet.getRow(rowStart);
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (headRow == null || rowStart == rowEnd) {
            return genericsList;
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        // 获取每行文件数据的值
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            List<String> list = Lists.newArrayList();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getCellValue(eachRow.getCell(k));
                    list.add(val);
                }
            }
            if (!CollectionUtils.isEmpty(list)) {
                T instance = tClass.newInstance();
                for (int j = 0; j < fields.size(); j++) {
                    Field field = tClass.getDeclaredField(fields.get(j));
                    field.setAccessible(true);
                    field.set(instance, list.get(j));
                }
                genericsList.add(instance);
            }
        }
        return genericsList;
    }

    /**
     * @Description：读取文件中第一个sheet页的数据，并返回List集合
     * @Author：fengcheng
     * @Date：2024/10/16 14:43
     * @Param：sheet
     * @ParamsType：[org.apache.poi.ss.usermodel.Sheet]
     * @Return：java.util.List<java.util.List<java.lang.String>>
     */
    private static List<List<String>> readSheetToList(Sheet sheet, Integer linesStart) {
        List<List<String>> excelList = new ArrayList<>();
        int rowStart;
        if (linesStart != 0) {
            rowStart = linesStart;
        } else {
            rowStart = sheet.getFirstRowNum();
        }
        int rowEnd = sheet.getLastRowNum();
        Row headRow = sheet.getRow(rowStart);
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (headRow == null || rowStart == rowEnd) {
            return excelList;
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            List<String> contentList = new ArrayList<>();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getCellValue(eachRow.getCell(k));
                    contentList.add(val);
                }
            }
            if (!CollectionUtils.isEmpty(contentList)) excelList.add(contentList);
        }
        return excelList;
    }

    /**
     * @Description：获取每个单元格的数据值
     * @Author：fengcheng
     * @Date：2024/10/16 14:43
     * @Param：cell
     * @ParamsType：[org.apache.poi.ss.usermodel.Cell]
     * @Return：java.lang.String
     */
    private static String getCellValue(Cell cell) {
        // 空白或空
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                String val = cell.getStringCellValue();
                if (val.trim().isEmpty()) {
                    return "";
                }
                return val.trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return new SimpleDateFormat(YYYY_MM_DD).format(cell.getDateCellValue());
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    return dataFormatter.formatCellValue(cell);
                }
            case BOOLEAN:
                return cell.getBooleanCellValue() + "";
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
            default:
                return "";
        }
    }

    /**
     * @Description：是否使用线程池进行导出多个Sheet页Excel文件 经过测试，在数据量大的情况下，使用线程池导出Excel文件，效率更高，数据量小的情况下，使用单线程和线程池导出Excel文件效率相差不大，此情况下优先使用单线程导出文件
     * @Author：fengcheng
     * @Date：2024/10/9 14:15
     * @Param：fileName 文件名称
     * @Param：sheetMap Excel导出文件数据
     * @Param：isThreadPools 是否使用线程池
     * @Param：threadPoolsObj 线程池对象
     * @ParamsType：[java.lang.String, java.util.Map<java.lang.String, java.util.List < java.util.List < java.lang.Object>>>, java.lang.Boolean, org.apache.poi.ss.formula.functions.T]
     * @Return：void
     */
    public static void exportManySheet(String fileName, Boolean isThreadPools, ThreadPoolTaskExecutor threadPoolsObj,
                                       Map<String, List<List<Object>>> sheetMap,
                                       HttpServletResponse response) throws BizException, IOException {
        // 是否使用线程池进行导出Excel文件，当列表数据量大时需要使用，不使用会导致内存溢出，此时将 excel 文件分块并行写入磁盘
        if (isThreadPools) {
            // 整个 Excel 表格 book 对象
            SXSSFWorkbook book = new SXSSFWorkbook();
            List<Future<Boolean>> futures = new ArrayList<>();
            // 每个 Sheet 页
            Set<Entry<String, List<List<Object>>>> entries = sheetMap.entrySet();
            for (Entry<String, List<List<Object>>> entry : entries) {
                List<List<Object>> sheetDataList = entry.getValue();
                Sheet sheet = book.createSheet(entry.getKey());
                Future<Boolean> future = threadPoolsObj.submit(() -> {
                    excelDataRead(entry, sheet, book, sheetDataList);
                    return true;
                });
                futures.add(future);
            }
            //等待线程处理完成
            for (Future<Boolean> f : futures) {
                try {
                    Boolean result = f.get();
                    if (!result) {
                        log.error("多线程构建导出的数据集合处理失败");
                    }
                } catch (Exception e) {
                    log.error("等待任务执行结果发生异常", e);
                }
            }
            writeDataToExcel(fileName, book, response);
        } else {
            export(fileName, sheetMap, response);
        }
    }

    /**
     * @Description：使用单线程导出多个Sheet页Excel文件
     * @Author：fengcheng
     * @Date：2024/10/9 17:53
     * @Param：fileName 文件名称
     * @Param：sheetMap Excel导出文件数据
     * @ParamsType：[java.lang.String, java.util.Map<java.lang.String, java.util.List < java.util.List < java.lang.Object>>>]
     * @Return：void
     */
    private static void export(String fileName, Map<String, List<List<Object>>> sheetMap, HttpServletResponse response) throws BizException, IOException {
        // 整个 Excel 表格 book 对象
        SXSSFWorkbook book = new SXSSFWorkbook();
        Set<Entry<String, List<List<Object>>>> entries = sheetMap.entrySet();
        for (Entry<String, List<List<Object>>> entry : entries) {
            excelDataRead(entry, book.createSheet(entry.getKey()), book, entry.getValue());
        }
        writeDataToExcel(fileName, book, response);
    }

    /**
     * @Description：Excel数据读取
     * @Author：fengcheng
     * @Date：2024/10/9 17:42
     * @Param：entry
     * @Param：sheet
     * @Param：book
     * @Param：sheetDataList
     * @ParamsType：[java.util.Map.Entry<java.lang.String,java.util.List<java.util.List<java.lang.Object>>>, org.apache.poi.ss.usermodel.Sheet, org.apache.poi.xssf.streaming.SXSSFWorkbook, java.util.List<java.util.List<java.lang.Object>>]
     * @Return：void
     */
    private static void excelDataRead(Entry<String, List<List<Object>>> entry, Sheet sheet, SXSSFWorkbook book, List<List<Object>> sheetDataList) throws BizException {
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        // 设置表头背景色（灰色）
        CellStyle headStyle = book.createCellStyle();
        headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        // 设置表身背景色（默认色）
        CellStyle rowStyle = book.createCellStyle();
        rowStyle.setAlignment(HorizontalAlignment.CENTER);
        rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置表格列宽度（默认为20个字节）
        sheet.setDefaultColumnWidth(20);
        // 创建合并算法数组
        int rowLength = sheetDataList.size();
        int columnLength = sheetDataList.get(0).size();
        int[][] mergeArray = new int[rowLength][columnLength];
        for (int i = 0; i < sheetDataList.size(); i++) {
            // 每个 Sheet 页中的行数据
            Row row = sheet.createRow(i);
            List<Object> rowList = sheetDataList.get(i);
            for (int j = 0; j < rowList.size(); j++) {
                // 每个行数据中的单元格数据
                Object o = rowList.get(j);
                int v = 0;
                if (o instanceof URL) {
                    // 如果要导出图片的话, 链接需要传递 URL 对象
                    setCellPicture(book, row, patriarch, i, j, (URL) o);
                } else {
                    Cell cell = row.createCell(j);
                    if (i == 0) {
                        // 第一行为表头行，采用灰色底背景
                        v = setCellValue(cell, o, headStyle);
                    } else {
                        // 其他行为数据行，默认白底色
                        v = setCellValue(cell, o, rowStyle);
                    }
                }
                mergeArray[i][j] = v;
            }
        }
        log.info(entry.getKey() + ",线程名称：" + Thread.currentThread().getName() + ",数据总数：{}", sheetDataList.size());
    }

    /**
     * @Description：将数据写入到Excel，并将其写入磁盘，释放资源；
     * @Author：fengcheng
     * @Date：2024/10/9 17:45
     * @Param：fileName
     * @Param：book
     * @ParamsType：[java.lang.String, org.apache.poi.xssf.streaming.SXSSFWorkbook]
     * @Return：void
     */
    private static void writeDataToExcel(String fileName, SXSSFWorkbook book, HttpServletResponse response) throws IOException {
        if (ObjectUtils.isEmpty(response)) {
            try {
                // 获取本地导出文件的全路径，LOCAL_FILE_PATH 是配置的是本地文件保存路径，配置的路径不存在，则自动创建
                String fullFilePath = getFileFullPath(fileName);
                FileOutputStream fos = new FileOutputStream(fullFilePath);
                ByteArrayOutputStream ops = new ByteArrayOutputStream();
                book.write(ops);
                fos.write(ops.toByteArray());
                deleteSXSSFTempFiles(book);
                fos.close();
            } catch (Exception e) {
                System.out.println("数据写入Excel文件发生异常 - 异常信息：" + e.getMessage());
                throw new BizException("数据写入Excel文件发生异常");
            }
        } else {
            // 通过浏览器下载文件
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            OutputStream outputStream = response.getOutputStream();
            book.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }
    }

    public static void exportTxtFile(String filePath, Object object) {
        try {
            File file = new File(filePath);
            FileOutputStream outputStream = new FileOutputStream(file);
            String testContent = object.toString();
            outputStream.write(testContent.getBytes());
        } catch (IOException e) {
            log.info("exportTxtFile.生成文件发生异常：{}", e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    private static int setCellValue(Cell cell, Object o, CellStyle style) {
        // 设置样式
        cell.setCellStyle(style);
        // 数据为空时
        if (o == null) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue("");
            return LIST_INDEX;
        }
        // 是否为字符串
        if (o instanceof String) {
            String s = o.toString();
            // 当数字类型长度超过8位时，改为字符串类型显示（Excel数字超过一定长度会显示为科学计数法）
            /*if (isNumeric(s) && s.length() < 8) {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(Double.parseDouble(s));
                return LIST_INDEX;
            } else {
                cell.setCellType(CellType.STRING);
                cell.setCellValue(s);
            }*/
            cell.setCellType(CellType.STRING);
            cell.setCellValue(s);
            if (s.equals(ROW_MERGE)) {
                return CELL_ROW_MERGE;
            } else if (s.equals(COLUMN_MERGE)) {
                return CELL_COLUMN_MERGE;
            } else {
                return LIST_INDEX;
            }
        }
        // 是否为字符串
        if (o instanceof Integer || o instanceof Long || o instanceof Double || o instanceof Float) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(o.toString()));
            return LIST_INDEX;
        }
        // 是否为Boolean
        if (o instanceof Boolean) {
            cell.setCellType(CellType.BOOLEAN);
            cell.setCellValue((Boolean) o);
            return LIST_INDEX;
        }
        // 如果是BigDecimal，则默认3位小数
        if (o instanceof BigDecimal) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(((BigDecimal) o).setScale(3, RoundingMode.HALF_UP).doubleValue());
            return LIST_INDEX;
        }
        // 如果是Date数据，则显示格式化数据
        if (o instanceof Date) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(formatDate((Date) o));
            return LIST_INDEX;
        }
        // 如果是其他，则默认字符串类型
        cell.setCellType(CellType.STRING);
        cell.setCellValue(o.toString());
        return LIST_INDEX;
    }

    private static void setCellPicture(SXSSFWorkbook wb, Row sr, Drawing<?> patriarch, int x, int y, URL url) {
        // 设置图片宽高
        sr.setHeight((short) (IMG_WIDTH * IMG_HEIGHT));
        // （jdk1.7版本try中定义流可自动关闭）
        try (InputStream is = url.openStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            byte[] buff = new byte[BYTES_DEFAULT_LENGTH];
            int rc;
            while ((rc = is.read(buff, 0, BYTES_DEFAULT_LENGTH)) > 0) {
                outputStream.write(buff, 0, rc);
            }
            // 设置图片位置
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, y, x, y + 1, x + 1);
            // 设置这个，图片会自动填满单元格的长宽
            anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String formatDate(Date date) {
        if (date == null) {
            return "";
        }
        SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
        return format.format(date);
    }

    private static void setSelect(Sheet sheet, Map<Integer, List<String>> selectMap) {
        if (selectMap == null || selectMap.isEmpty()) {
            return;
        }
        Set<Entry<Integer, List<String>>> entrySet = selectMap.entrySet();
        for (Entry<Integer, List<String>> entry : entrySet) {
            int y = entry.getKey();
            List<String> list = entry.getValue();
            if (list == null || list.isEmpty()) {
                continue;
            }
            String[] arr = new String[list.size()];
            for (int i = 0; i < list.size(); i++) {
                arr[i] = list.get(i);
            }
            DataValidationHelper helper = sheet.getDataValidationHelper();
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, y, y);
            DataValidationConstraint dvc = helper.createExplicitListConstraint(arr);
            DataValidation dv = helper.createValidation(dvc, addressList);
            if (dv instanceof HSSFDataValidation) {
                dv.setSuppressDropDownArrow(false);
            } else {
                dv.setSuppressDropDownArrow(true);
                dv.setShowErrorBox(true);
            }
            sheet.addValidationData(dv);
        }
    }

    private static String getString(String s) {
        if (s == null) {
            return "";
        }
        if (s.isEmpty()) {
            return s;
        }
        return s.trim();
    }

    /**
     * @Description：删除POI临时文件
     * @Author： ext.fengcheng
     * @Date：2023/9/15 14:10
     * @Param： [workbook]
     * @Return： void
     */
    public static void deleteSXSSFTempFiles(SXSSFWorkbook workbook) throws NoSuchFieldException, IllegalAccessException {
        int numberOfSheets = workbook.getNumberOfSheets();
        // 遍历所有sheet临时文件
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheetAt = workbook.getSheetAt(i);
            // 仅当工作表由流写入时才删除
            if (sheetAt instanceof SXSSFSheet) {
                SheetDataWriter sdw = (SheetDataWriter) getPrivateAttribute(sheetAt, "_writer");
                File f = (File) getPrivateAttribute(sdw, "_fd");
                try {
                    f.delete();
                } catch (Exception ex) {
                    log.info("无法删除数据导出生成的临时文件");
                }
            }
        }
    }

    public static Object getPrivateAttribute(Object containingClass, String fieldToGet) throws NoSuchFieldException, IllegalAccessException {
        // 获取containingClass实例的字段
        Field declaredField = containingClass.getClass().getDeclaredField(fieldToGet);
        // 将其设置为可访问
        declaredField.setAccessible(true);
        // 访问它
        Object get = declaredField.get(containingClass);
        return get;

    }

    public static String getFileName(String fileName) {
        String fileNamePrefix = fileName.substring(0, fileName.lastIndexOf("-"));
        String fileSuffix = fileName.substring(fileName.lastIndexOf("-") + 1);
        String newFileSuffix = Integer.toString(Integer.parseInt(fileSuffix) + 1);
        return fileNamePrefix + "-" + newFileSuffix;
    }

    public static String getFileFullPath(String fileName) {
        File filePath = new File(LOCAL_FILE_PATH);
        String fullFilePath;
        if (!filePath.exists()) {
            filePath.mkdirs();
        }
        fullFilePath = filePath.getPath() + File.separator + fileName;
        return fullFilePath;
    }

    /**
     * @Description：将multipartFile转化为file对象
     * @Author：fengcheng
     * @Date：2024/10/17 17:33
     * @Param：multipartFile
     * @ParamsType：[org.springframework.web.multipart.MultipartFile]
     * @Return：java.io.File
     */
    public static File toFile(MultipartFile multipartFile) throws IOException {
        // 如果multipartFile为空，则直接返回null
        if (multipartFile == null) {
            return null;
        }
        // 创建一个临时的文件
        File file = File.createTempFile("temp", multipartFile.getOriginalFilename());
        multipartFile.transferTo(file);
        return file;
    }

    /**
     * @Description：校验文件大小，默认为 10MB
     * @Author：fengcheng
     * @Date：2024/10/17 17:29
     * @Param：file
     * @ParamsType：[java.io.File]
     * @Return：boolean
     */
    public static boolean verifyTheFileSize(MultipartFile multipartFile) throws IOException {
        boolean verifyResult = true;
        File file = toFile(multipartFile);
        if (file.length() > FILE_SIZE) {
            log.info("文件大小过大！请上传小于或等于{}MB的文件。", 10);
            verifyResult = false;
        }
        return verifyResult;
    }

    /**
     * @Description：下载文件导入模板
     * @Author：fengcheng
     * @Date：2024/12/11 09:27
     * @Param：filePath 在项目资源路径下的文件路径
     * @Param：fileName 下载文件名称
     * @Param：fileSuffix 文件后缀
     * @Param：response
     * @ParamsType：[java.lang.String, java.lang.String, java.lang.String, javax.servlet.http.HttpServletResponse]
     * @Return：void
     */
    public static void downloadFileImportTemplate(String filePath, String fileName, String fileSuffix, HttpServletResponse response) {
        log.info("downloadFileImportTemplate.下载文件导入模板");
        try {
            InputStream fis = Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            response.setContentType("application/binary;charset=UTF-8");
            response.setHeader("Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8") + fileSuffix);
            ServletOutputStream out = null;
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            log.error("下载文件导入模板，异常信息：{}", e.getMessage(), e);
            throw new BizException("下载文件导入模板");
        }
    }
}